package cn.sendto.bussystem.dbhelper;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


/**
 * ClassName: DBHelper <br/> 
 * Function: CRUD <br/> 
 * date: 2017年5月7日 下午1:01:05 <br/> 
 * @author 阿森 
 * @version  
 * @since JDK 1.8
 */
public class DBHelper {
	//执行sql语句的预编译对象
	private PreparedStatement psmd;
	private ResultSet rs;
	private int rows = -1;
	private Connection conn;
	private static String confile = "jdbc.properties";
	private static Properties p = null;
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
	//private static DataSource ds = null;
	static {
		InputStream inputStream = null;
		try {
			p = new Properties();
			confile = DBHelper.class.getClassLoader().getResource("").getPath() + confile;
			File file = new File(confile);
			inputStream = new BufferedInputStream(new FileInputStream(file));
			p.load(inputStream);
			Class.forName(p.getProperty("driver"));
		} catch (Exception e) {

		} finally {
			if (inputStream != null) {
				try {
					inputStream.close();
				} catch (IOException e) {

					e.printStackTrace();
				}
			}
		}

	}

	/**
	 * 
	 * getConnection:(获取连接). <br/>
	 * 
	 * @author 阿森 
	 * @throws SQLException 
	 * @since JDK 1.8
	 */
	private void getConnection() throws SQLException {
		conn = tl.get();
		try {
			if (conn == null) {
				  conn = DriverManager.getConnection(p.getProperty("url"), p.getProperty("user"), p.getProperty("password")) ;
				}
		} catch (Exception e) {
			e.printStackTrace();
		}
		tl.set(conn);
	}

	/**
	 * close:(关闭连接). <br/>
	 * @author 阿森  
	 * @since JDK 1.8
	 */
	public void close() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if(psmd!= null){
				psmd.close();
				psmd = null;
			}
			if(conn!= null&& !conn.isClosed()){
				conn.close();
				conn = null;
				tl.remove();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 
	 * executeUpdate:(执行更新数据). <br/>
	 * 
	 * @author 阿森 
	 * @param sql
	 * @param objs
	 * @return
	 * @throws SQLException 
	 * @since JDK 1.8
	 */
	public int executeUpdate(String sql, Object...objs) throws SQLException {
		this.getConnection();
		//ִ创建PreparedStatement对象
		psmd = conn.prepareStatement(sql);
		//设置sql语句参数
		this.setParam(objs);
		//执行SQL
		rows = psmd.executeUpdate();
		//返回影响的行数
		return rows;
	}
	/**
	 * 
	 * setParam:(设置参数). <br/>
	 * 
	 * @author 阿森 
	 * @param objs
	 * @throws SQLException 
	 * @since JDK 1.8
	 */
	private void setParam(Object...objs)throws SQLException {
		  if(objs!=null&&objs.length>0){
			  for(int i=0;i<objs.length;i++){
				  psmd.setObject(i+1, objs[i]);
			  }
		  }
	}
	/**
	 * 
	 * executeSave:(执行保存数据). <br/>
	 * 
	 * @author 阿森 
	 * @param sql
	 * @param objs
	 * @return
	 * @throws SQLException 
	 * @since JDK 1.8
	 */
	public int executeSave(String sql,Object...objs)throws SQLException{
		this.getConnection();
		psmd  = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		this.setParam(objs);
		rows = psmd.executeUpdate();
	    rs = psmd.getGeneratedKeys();
	    if(rs.next()){
	    	return rs.getInt(1);
	    }
		return -1;
	}
	/**
	 * 
	 * executeQuery:(查询数据). <br/>
	 * 
	 * @author 阿森 
	 * @param sql
	 * @param mapper
	 * @param objs
	 * @return
	 * @throws Exception 
	 * @since JDK 1.8
	 */
	public <T>List<T>executeQuery(String sql,IRowMapper<T> mapper,Object...objs) throws Exception{
		 this.getConnection();
		 psmd = conn.prepareStatement(sql);
		this.setParam(objs);
		rs = psmd.executeQuery();
		List<T> tlist = new ArrayList<T>();
		while(rs.next()){
			T t = mapper.rowMapper(rs);
		   tlist.add(t);
		}
		
		return tlist;
		
	}
	/**
	 * 
	 * executeScalar:(这里用一句话描述这个方法的作用). <br/>
	 * 
	 * @author 阿森 
	 * @param sql
	 * @param objs
	 * @return
	 * @throws SQLException 
	 * @since JDK 1.8
	 */
	  public Object executeScalar (String sql, Object[]objs) throws SQLException {
		   this.getConnection();
		   psmd = conn.prepareStatement(sql);
		   this.setParam(objs);
		   rs = psmd.executeQuery();
		   Object object = null;
		   if(rs.next()){
			   object = rs.getObject(1);
		   }
		  return object;
	  }
	  /**
	   * 
	   * begins:(开启事务). <br/>
	   * 
	   * @author 阿森 
	   * @throws Exception 
	   * @since JDK 1.8
	   */
	  public void begins() throws Exception{
		  this.getConnection();
		  tl.get().setAutoCommit(false);
	  }
	  /**
	   * 
	   * commit:(提交). <br/>
	   * 
	   * @author 阿森 
	   * @throws Exception 
	   * @since JDK 1.8
	   */
	  public void commit() throws Exception{
         tl.get().commit();//�ύ����
	  }
	  /**
	   *
	   * rollback:(回滚). <br/>
	   * 
	   * @author 阿森 
	   * @throws Exception 
	   * @since JDK 1.8
	   */
	 public void rollback() throws Exception{
		 tl.get().rollback();
	 }
} 
